package com.geping.etl.common.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import com.geping.etl.common.entity.Sys_Department;

@Repository
public interface Sys_DepartmentRepository extends PagingAndSortingRepository<Sys_Department, Integer>{
	
	
	//查询所有未删除的部门
	@Query("select s from Sys_Department s where s.isDelete = 'N'")
	public List<Sys_Department> findAllSysDepartment();
	
	//根据用户名查询部门信息
	@Query("select s from Sys_Department s where s.isDelete = 'N' and s.departmentName like concat ('%',?1,'%')")
	public List<Sys_Department> getSys_DepartmentByLikeDepartmentName(String departmentName);

	//根据部门编号查询部门信息
	@Query("select s from Sys_Department s where s.isDelete = 'N' and s.departmentId = ?1")
	public Sys_Department getSys_DepartmentByDepartmentId(String departmentId);
	
	//获取最大ID数
	@Query("select max(s.id) from Sys_Department s")
	public Integer findMaxId();
	
	//修改部门信息
	@Modifying
	@Query("update Sys_Department s set s.departmentName = ?2,s.description = ?3,s.departmentId = ?4 where s.id = ?1")
	public int updateSys_Department(Integer id_edit,String departmentName,String description,String departmentId);
	
	//逻辑删除部门
	@Modifying
	@Query("update Sys_Department s set s.isDelete = 'Y' where s.id = ?1")
	public int deleteSys_Department(Integer id);


	//设置部门ID
	@Modifying
	@Query("update Sys_Department s set s.departmentId = ?2 where s.id = ?1")
	public int updateDepartmentID(Integer id_edit,String departmentId);

	@Query("select s from Sys_Department s where s.departmentName = ?1 and s.isDelete = 'N'")
	List<Sys_Department> findByDepartmentName(String departmentName);

	@Query("select s from Sys_Department s where s.departmentId like concat (?1,'%') and s.departmentName= ?2 and s.isDelete = 'N'")
	List<Sys_Department> getSys_DepartmentByLikeDepartmentIdAndDepartmentName(String departmentId, String departmentName);

	@Query("select s from Sys_Department s where s.description = ?1 and s.departmentName= ?2 and s.isDelete = 'N'")
	List<Sys_Department> findByDescriptionAndDepartmentName(String description, String departmentName);
}
